在 PostgreSQL 中，資料庫的備份和恢復是非常重要的
它能夠保護資料庫中的數據，防止因各種原因（如硬體故障、人為錯誤等）造成的數據損失

所以今天就來介紹在 PostgreSQL 的世界中，我們該如何去備份我們的資料庫，以及如何用這些已經備份好的資料來恢復我們的資料庫

PostgreSQL 提供了幾種備份方法
1. SQL 備份    2. 物理備份

SQL 備份 (pg_dump / pg_dumpall)

SQL 備份是使用 pg_dump / pg_dumpall 工具生成 SQL 腳本的過程
這個腳本包含了建立資料庫對象和插入數據的 SQL 語句
這種備份方法的優點是移植性強，可以輕易地在不同的系統和 PostgreSQL 版本間轉移

1. 首先我們先進入 psql 終端介面

$ psql -U postgres

2. 使用 \l 列出 postgreSQL 中的所有 DB

postgres=# \l

                                                             List of databases
               Name               |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
----------------------------------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
demo_db                           | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | en-US      | icu             |
......
......
......

3. 使用 \q 退出 psql 終端介面，使用 pg_dump 指令備份我們的資料庫

• 找到我們要備份的 DB 使用 pg_dump 指令
• 後面的檔案名稱可以隨便取，但是要是 sql 檔案格式
• 後面沒有指定路徑，就是會在當前的資料夾生成檔案

$ pg_dump demo_db > backup_demo_db.sql

4. 使終端機指令 ls -al 查看使否有成功

$ ls -al

total 97568
drwxr-xr-x@  3 chan  staff        96 Oct  1 18:39 .
drwxr-xr-x@ 35 chan  staff      1120 Oct  1 18:38 ..
-rw-r--r--@  1 chan  staff  49954626 Oct  1 18:39 backup_demo_db.sql

備份所有資料庫和角色 (pg_dumpall)

• 但是大多數的情況之後用到 pg_dump

$ pg_dumpall > all_db_backup.sql

or

• 也可以配合 -U 以及 -f
• -U 確保這個 postgreSQL 用戶有權限可以將所有的 db 備份出來
• -f 則是確認輸出成檔案， (> 或是 -f 擇一 因為兩者的作用都是指定輸出成一個檔案)

$ pg_dumpall -U username -f path/to/all_db_backup.sql

物理備份 (pg_basebackup)

物理備份涉及到復制資料庫的物理文件，它比 SQL 備份更快，並且可以用於大型資料庫，這種備份方法的缺點是移植性差，主要用於災難恢復

$ pg_basebackup -D /path/to/backup -F t -Z 9 -P -v
或是

$ pg_basebackup -D backup -F t -Z 9 -P -v

差別是在沒有指定路徑的話，會直接生成在當下資料夾

-D 或 --pgdata：指定備份的目標目錄。
-F 或 --format：指定輸出格式，可以是純文本（p）或 tar 格式（t）。
-Z：指定壓縮級別，範圍是 0（無壓縮）到 9（最大壓縮）。
-P 或 --progress：顯示進度信息。
-v 或 --verbose：顯示詳細的命令行輸出。

完成之後使用終端機指查看，他會建立一個名稱為 backup 的資料夾，
並進入查看，就可以看到我們的所有資料庫的壓縮檔

$ ls -al

drwx------@  5 chan  staff       160 Oct  1 19:02 backup


$ cd backup/
$ ls -al

total 168008
drwx------@ 5 chan  staff       160 Oct  1 19:02 .
drwxr-xr-x@ 7 chan  staff       224 Oct  1 19:01 ..
-rw-------@ 1 chan  staff   2003182 Oct  1 19:02 backup_manifest
-rw-------@ 1 chan  staff  81517998 Oct  1 19:02 base.tar.gz
-rw-------@ 1 chan  staff     17111 Oct  1 19:02 pg_wal.tar.gz

恢復資料

1. SQL 備份恢復 (使用 pg_dump 或 pg_dumpall 創建的備份)

• pg_dump 針對單一個資料庫資料的恢復

  $ psql -U username -d db_name <  backup_demo_db.sql
  

• pg_dumpall 針對整個 RDBMS 的恢復

  $ psql -f /path/to/backup.sql
  

2. 物理備份恢復 (使用 pg_basebackup 創建的備份)

對於通過 pg_basebackup 創建的物理備份，恢復的基本步驟是將備份的資料夾替換到 PostgreSQL 的資料夾，然後啟動 PostgreSQL 服務

1. 停止 PostgreSQL 服務
   在進行物理備份恢復之前，需要停止運行中的 PostgreSQL 服務。

   sudo systemctl stop postgresql
   

2. 移動當前資料夾
   為避免覆蓋當前的資料夾，所以要將其移動到不同的位置。

   sudo mv /var/lib/postgresql/15/main /var/lib/postgresql/15/main.bak
   

   這裡，/var/lib/postgresql/15/main 應該是 PostgreSQL 資料夾的實際路徑(可能會因不同的安裝方式而不同)，
   並且 15 應該是 PostgreSQL 版本。

3. 恢復備份
   現在，可以將備份的資料夾移動回原來的位置

   sudo cp -R /path/to/backup /var/lib/postgresql/13/main
   

   在這裡，/path/to/backup 是您的備份資料夾的路徑。

4. 確保權限正確
   需要確認新的資料夾的所有權和權限設置正確

   sudo chown -R postgres:postgres /var/lib/postgresql/13/main
   

5. 啟動 PostgreSQL 服務
   確保一切都設置正確後，就可以重新啟動 PostgreSQL 服務

   sudo systemctl start postgresql
   

6. 檢查資料庫
   最後登入 PostgreSQL，並檢查你的資料庫以確保所有資料都已成功恢復。

其他備份方式

4. 快照備份：
   有一些雲端服務商和文件系統支援建立資料的即時快照，這可以作為一種備份的方式

5. 雲服務備份：
   許多雲端服務平台提供自動備份和恢復服務，例如 AWS RDS、Google Cloud SQL 等

6. 雜項工具和服務：
   有許多第三方工具和服務也提供資料庫備份和恢復解決方案，例如 Barman、pgBackRest 等

每種備份策略都有其使用場景，選擇哪種策略取決於特定的需求，例如恢復時間目標（RTO）、恢復點目標（RPO）、儲存成本、操作複雜性等。在實際操作中，綜合使用多種策略通常會更加靈活和可靠。

當您使用 pg_basebackup 進行物理備份時，您實際上是在備份整個 PostgreSQL 數據目錄的內容，這包含了所有的數據庫和事務日誌文件。這種備份方法通常用於災難恢復，並且允許您快速恢復整個數據庫系統。